
import sys
import pyodbc,traceback
class sqlseverDB:
    
    '''
    操作SQL Server的类
    '''

    def __init__(self, DRIVER, SERVER, DATABASE, UID, PWD, charset='utf-8'):
        
        '''
        功能：初始化配置信息
        DRIVER：  数据驱动器
        SERVER：  数据服务器
        DATABASE：数据库
        UID：     登录名
        PWD：     登录密码
        charset:  字符编码
        '''
        self.DRIVER = DRIVER
        self.SERVER = SERVER
        self.DATABASE = DATABASE
        self.UID = UID
        self.PWD = PWD
        self.chartset = charset
    
    def __getConnect(self):
        '''
        功能：利用配置信息连接SQL Server
        '''
        try:
            conn = pyodbc.connect(DRIVER=self.DRIVER,
                                       SERVER=self.SERVER,
                                       DATABASE=self.DATABASE,
                                       UID=self.UID,
                                       PWD=self.PWD,
                                       charset=self.chartset)
            
        except Exception as ex:
            print('SQL Server connecting error,reason is:'+str(ex))
            sys.exit()
        
        return conn

    def ExecQuery(self, sql,params=None):

        '''
        功能：执行查询语句，返回结果集
        sql：要执行的SQL语句
        '''
        conn=self.__getConnect()
        cur = conn.cursor()
        try:
            if params:
                cur.execute(sql,params)
            else:
                cur.execute(sql)
            rows = cur.fetchall()

        except pyodbc.Error as ex:
            print('SQL Server.Error:%s' % str(ex))
            sys.exit()
        cur.close()
        conn.close()
        return rows

    def ExecQuerydict(self, sql,params=None):

        '''
        功能：执行查询语句，返回字典结果集
        sql：要执行的SQL语句
        '''
        conn=self.__getConnect()
        cur = conn.cursor()
        try:
            if params:
                cur.execute(sql,params)
            else:
                cur.execute(sql)
            rows = cur.fetchall()

        except pyodbc.Error as ex:
            print('SQL Server.Error:%s' % str(ex))
            sys.exit()
        description=cur.description
        cur.close()
        conn.close()
        rows2 = [dict(zip([column[0] for column in description], row)) for row in rows]
        return rows2
    

    def ExecNoQuery(self, sql,params=None):
        '''
        功能：执行查询语句，如Create,Insert,Delete,update,drop等。
        sql：要执行的SQL语句
        '''
        conn=self.__getConnect()
        cur = conn.cursor()

        try:
            if params:
                cur.execute(sql,params)
            else:
                cur.execute(sql)
            conn.commit()
            status=cur.rowcount
            
        except pyodbc.Error as ex:
            #traceback.print_exc()
            print('SQL Server.Error:%s' % ex)
            status=0
        cur.close()
        conn.close()
        return status
    
    def ExecNoQuery2(self, sqls):
        '''
        功能：执行查询语句，如Create,Insert,Delete,update,drop等。
        sql：要执行的SQL语句
        '''
        cur = self.conn.cursor()
        status=0
        try:
            for sql in sqls:
                cur.execute(sql)
                status+=cur.rowcount
            self.conn.commit()
            
        except pyodbc.Error as ex:
            print('SQL Server.Error:%s' % ex)
            status=0
        cur.close()
        return status

    def call_procedure(self,proce_name):

        '''调用存储过程'''

        # 设置数据库连接参数

        try:
            # 连接数据库
            conn=self.__getConnect()
            cur = conn.cursor()

            # 执行存储过程
            cur.execute(f"{{CALL {proce_name}}}")

            # 提交事务
            conn.commit()
            status=cur.rowcount

        except pyodbc.Error as e:
            print(f"存储过程执行失败: {e}")
            status=0
        finally:
            # 关闭连接
            conn.close()
        
        return status

    
    def insert_datas(self,datas,tb_name,fields):

        '''
        功能：批量插入
        datas:要插入的数据列表
        '''

        field_str=','.join(fields)
        InsertSQL = f'insert into [dbo].[{tb_name}]({field_str}) values'
        j=0
        sqls=[]
        params=[]
        for r in datas:
            if len(r)!=len(fields):
                print(f'{r}值数量不够赋值字段数')
                continue
            InsertSQL+='('
            whstr= ''

            for ivalue in r:
                whstr+='?,'
                params.append(ivalue)
            InsertSQL+=whstr
            InsertSQL=InsertSQL[:-1]
            InsertSQL+='),'
            j+=1
            if j>=200:
                InsertSQL=InsertSQL[:-1]
                sqls.append((InsertSQL,params))
                InsertSQL = f'insert into [dbo].[{tb_name}]({field_str}) values'
                params=[]
                j=0
        
        if InsertSQL!=f'insert into [dbo].[{tb_name}]({field_str}) values':
            InsertSQL=InsertSQL[:-1]
            sqls.append((InsertSQL,params))

        success=0
        fail=0
        for sqlstr,para in sqls:
            yss=self.ExecNoQuery(sqlstr,para)
            if yss:
                success+=yss
            else:
                fail+=1

        return success

# ms = sqlseverDB('{SQL Server}', '199.33.126.84,1433', 'shopeecoid', 'sa', 'Y3GwLHaP8zQ0seZG')

# r=ms.ExecQuery('select top 1 [id],[PingYu],[picdir] from [comment] where [YiShiYong]=0 and [PingLunShenHeZhuangTai]=1 and [isDwonloadImg]=1 and [zjpid]=?',('1022375790-22771956115',))

# print(r)